package com.fundcrawler.repository;

import com.fundcrawler.entity.FundNav;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

@Repository
public interface FundNavRepository extends JpaRepository<FundNav, Integer> {

    @Query("SELECT f FROM FundNav f WHERE f.fundCode = :code ORDER BY f.date DESC")
    Page<FundNav> findByFundCode(@Param("code") String code, Pageable pageable);

    @Query(value = "SELECT * FROM fund_nav WHERE fund_code = :code ORDER BY date DESC LIMIT 1", nativeQuery = true)
    FundNav findLatestByFundCode(@Param("code") String code);

    @Query("SELECT f FROM FundNav f WHERE f.fundCode = :code and f.date=:date")
    FundNav findByFundCodeDate(@Param("code") String code, @Param("date") LocalDate date);

    @Query("SELECT COUNT(DISTINCT f.fundCode) FROM FundNav f")
    long countDistinctFundCodes();

    @Query(value = "SELECT '成立起' AS period, MIN(date) AS start_date, ROUND(SUM(CASE WHEN unit_nav > :unitNav THEN 100.0 ELSE 0 END) / COUNT(*), 2) AS undervaluation_rate, fund_code, fund_name,min(unit_nav) unitNavMin, max(unit_nav) unitNavMax,ROUND(avg(unit_nav), 4) unitNavAvg, 1 unitNavStart FROM fund_nav WHERE fund_code = :fundCode\n" +
            "    UNION ALL\n" +
            "    SELECT '5年' AS period, MIN(date) AS start_date, ROUND(SUM(CASE WHEN unit_nav > :unitNav THEN 100.0 ELSE 0 END) / COUNT(*), 2) AS undervaluation_rate, fund_code, fund_name,min(unit_nav) unitNavMin, max(unit_nav) unitNavMax,ROUND(avg(unit_nav), 4) unitNavAvg, sum(case when date=DATEADD('YEAR', -5, sysdate()) then unit_nav else 0 end) unitNavStart FROM fund_nav WHERE fund_code = :fundCode AND date >= DATEADD('YEAR', -5, SYSDATE())\n" +
            "    UNION ALL\n" +
            "    SELECT '3年' AS period, MIN(date) AS start_date, ROUND(SUM(CASE WHEN unit_nav > :unitNav THEN 100.0 ELSE 0 END) / COUNT(*), 2) AS undervaluation_rate, fund_code, fund_name,min(unit_nav) unitNavMin, max(unit_nav) unitNavMax,ROUND(avg(unit_nav), 4) unitNavAvg, sum(case when date=DATEADD('YEAR', -3, sysdate()) then unit_nav else 0 end) unitNavStart FROM fund_nav WHERE fund_code = :fundCode AND date >= DATEADD('YEAR', -3, SYSDATE())\n" +
            "    UNION ALL\n" +
            "    SELECT '1年' AS period, MIN(date) AS start_date, ROUND(SUM(CASE WHEN unit_nav > :unitNav THEN 100.0 ELSE 0 END) / COUNT(*), 2) AS undervaluation_rate, fund_code, fund_name,min(unit_nav) unitNavMin, max(unit_nav) unitNavMax,ROUND(avg(unit_nav), 4) unitNavAvg, sum(case when date=DATEADD('YEAR', -1, sysdate()) then unit_nav else 0 end) unitNavStart FROM fund_nav WHERE fund_code = :fundCode AND date >= DATEADD('YEAR', -1, SYSDATE())\n" +
            "    UNION ALL\n" +
            "    SELECT '6个月' AS period, MIN(date) AS start_date, ROUND(SUM(CASE WHEN unit_nav > :unitNav THEN 100.0 ELSE 0 END) / COUNT(*), 2) AS undervaluation_rate, fund_code, fund_name,min(unit_nav) unitNavMin, max(unit_nav) unitNavMax,ROUND(avg(unit_nav), 4) unitNavAvg, sum(case when date=DATEADD('MONTH', -6, sysdate()) then unit_nav else 0 end) unitNavStart FROM fund_nav WHERE fund_code = :fundCode AND date >= DATEADD('MONTH', -6, SYSDATE())\n" +
            "    UNION ALL\n" +
            "    SELECT '3个月' AS period, MIN(date) AS start_date, ROUND(SUM(CASE WHEN unit_nav > :unitNav THEN 100.0 ELSE 0 END) / COUNT(*), 2) AS undervaluation_rate, fund_code, fund_name,min(unit_nav) unitNavMin, max(unit_nav) unitNavMax,ROUND(avg(unit_nav), 4) unitNavAvg, sum(case when date=DATEADD('MONTH', -3, sysdate()) then unit_nav else 0 end) unitNavStart FROM fund_nav WHERE fund_code = :fundCode AND date >= DATEADD('MONTH', -3, SYSDATE())\n" +
            "\tUNION ALL\n" +
            "    SELECT '1个月' AS period, MIN(date) AS start_date, ROUND(SUM(CASE WHEN unit_nav > :unitNav THEN 100.0 ELSE 0 END) / COUNT(*), 2) AS undervaluation_rate, fund_code, fund_name,min(unit_nav) unitNavMin, max(unit_nav) unitNavMax,ROUND(avg(unit_nav), 4) unitNavAvg, sum(case when date=DATEADD('MONTH', -1, sysdate()) then unit_nav else 0 end) unitNavStart FROM fund_nav WHERE fund_code = :fundCode AND date >= DATEADD('MONTH', -1, SYSDATE())", nativeQuery = true)
    List<Map> findValuationStatsRaw(
            @Param("fundCode") String fundCode,
            @Param("unitNav") Double unitNav);

}
